﻿using static zijian666.Data.DbConnectionExtensions;

namespace zijian666.Data;

/// <summary>
/// <seealso cref="IDbConnection"/> 相关的异步扩展方法
/// </summary>
public static partial class DbConnectionAsyncExtensions
{
    /// <summary>
    /// 释放组件
    /// </summary>
    /// <param name="compoent"></param>
    /// <param name="connectionOnlyClose"> 如果 <paramref name="compoent"/> 是 <seealso cref="IDbConnection"/>,则 <c>true</c> 表示仅关闭连接, 不释放, 否则为 <c>false</c> </param>
    internal static async Task ComponentDisposeAsync(this object compoent, bool connectionOnlyClose = true)
    {
        if (compoent is null)
        {
            return;
        }
        try
        {
            switch (compoent)
            {
                case DbConnection connection:
                    if (connection.IsOpen())
                    {
                        await connection.SafeCloseAsync();
                    }
                    if (!connectionOnlyClose)
                    {
                        await connection.DisposeAsync();
                    }
                    break;
                case IDbConnection connection:
                    if (connection.IsOpen())
                    {
                        connection.Close();
                    }
                    if (!connectionOnlyClose)
                    {
                        connection.Dispose();
                    }
                    break;
                case DbDataReader reader:
                    if (!reader.IsClosed)
                    {
                        await reader.CloseAsync();
                    }
                    await reader.DisposeAsync();
                    break;
                case IDataReader reader:
                    if (!reader.IsClosed)
                    {
                        reader.Close();
                    }
                    reader.Dispose();
                    break;
                case IAsyncDisposable disposable:
                    await disposable.DisposeAsync();
                    break;
                case IDisposable disposable:
                    disposable.Dispose();
                    break;
                case Func<Task> disposable:
                    await disposable();
                    break;
                case Action disposable:
                    disposable();
                    break;
                default:
                    break;
            }
        }
        catch (Exception e)
        {
            Diagnostic.Error(e);
        }
    }

    /// <summary>
    /// 如果数据库连接当前为关闭状态, 则打开返回 <code>true</code>, 否则返回 <code>false</code>
    /// <para> 如果 <paramref name="conn"/> 为null, 也返回 <code>false</code> </para>
    /// </summary>
    /// <param name="conn"></param>
    public static Task<bool> OpenIfClosedAsync([NotNullWhen(true)] this DbConnection? conn, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _OpenIfClosedAsync(conn, cancellationToken);
    }
    private static async Task<bool> _OpenIfClosedAsync([NotNullWhen(true)] this DbConnection? conn, CancellationToken cancellationToken = default)
    {
        if (conn is null)
        {
            return false;
        }
        var timestamp = Stopwatch.GetTimestamp();
        try
        {
            if (conn.NotOpen())
            {
                var state = conn.State;
                await conn.OpenAsync(cancellationToken);
                _ = await DbInterceptors.OnConnectionStateChanging(() => new(conn, state, timestamp, cancellationToken));
                return true;
            }
            return false;
        }
        catch (Exception ex)
        {
            await DbInterceptors.OnExceptionOccurred(() => new(conn, ex, timestamp, cancellationToken));
            throw;
        }
    }


    /// <summary>
    /// 关闭数据库连接, 不返回任何错误
    /// </summary>
    /// <param name="conn"></param>
    private static async Task SafeCloseAsync(this DbConnection? conn)
    {
        if (conn is null)
        {
            return;
        }
        var timestamp = Stopwatch.GetTimestamp();
        try
        {
            if (conn.NotClosed())
            {
                var state = conn.State;
                await conn.CloseAsync();
                _ = await DbInterceptors.OnConnectionStateChanging(() => new(conn, state, timestamp, CancellationToken.None));
            }
        }
        catch (Exception ex)
        {
            await DbInterceptors.OnExceptionOccurred(() => new(conn, ex, timestamp, CancellationToken.None));
        }
    }

    /// <summary>
    /// 执行委托 <paramref name="func"/>
    /// <para> 如果数据库连接未打开, 则打开, 执行完毕后关闭连接 </para>
    /// <para> 如果数据库连接已打开则直接执行委托, 执行完毕后也不关闭连接 </para>
    /// </summary>
    public static Task<T> ExecuteAsync<T>(this DbConnection conn, Func<ConnectionScope, Task<T>> func, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteAsync(conn, func, cancellationToken);
    }
    internal static async Task<T> _ExecuteAsync<T>(this DbConnection conn, Func<ConnectionScope, Task<T>> func, CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(conn);
        ArgumentNullException.ThrowIfNull(func);

        await using var opening = conn.OpenScope();
        try
        {
            await conn._OpenIfClosedAsync(cancellationToken);
            if (System.Transactions.Transaction.Current is { } trans && conn is DbConnection db)
            {
                db.EnlistTransaction(trans);
            }
            return await func(opening);
        }
        catch (Exception ex)
        {
            await DbInterceptors.OnExceptionOccurred(() => new(conn, ex, opening.Timestamp, cancellationToken) { Scope = opening });
            throw;
        }
    }

    /// <summary>
    /// 执行委托 <paramref name="action"/>
    /// <para> 如果数据库连接未打开, 则打开, 执行完毕后关闭连接 </para>
    /// <para> 如果数据库连接已打开则直接执行委托, 执行完毕后也不关闭连接 </para>
    /// </summary>
    public static Task ExecuteAsync(this DbConnection conn, Func<ConnectionScope, Task> action, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteAsync(conn, action, cancellationToken);
    }
    internal static async Task _ExecuteAsync(this DbConnection conn, Func<ConnectionScope, Task> action, CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(conn);

        if (action is null)
        {
            return;
        }

        await using var opening = conn.OpenScope();
        try
        {
            await conn._OpenIfClosedAsync(cancellationToken);
            if (System.Transactions.Transaction.Current is { } trans && conn is DbConnection db)
            {
                db.EnlistTransaction(trans);
            }
            await action(opening);
        }
        catch (Exception ex)
        {
            await DbInterceptors.OnExceptionOccurred(() => new(conn, ex, opening.Timestamp, cancellationToken) { Scope = opening });
            throw;
        }
    }

    /// <summary>
    /// 执行事务, 除非 <paramref name="action"/> 抛出异常, 否则直接提交
    /// <para> 如果数据库连接未打开, 则打开, 执行完毕后关闭连接 </para>
    /// <para> 如果数据库连接已打开则直接执行委托, 执行完毕后也不关闭连接 </para>
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="action"></param>
    public static Task TransactionAsync(this DbConnection conn, Func<Task> action, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteAsync(conn, async opening =>
        {
            using var tran = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
            opening.Add(tran);
            await action();
            tran.Complete();
        }, cancellationToken);
    }

    /// <summary>
    /// 使用 sql语句(<paramref name="sql"/>) 创建数据库执行命令(<see cref="IDbCommand"/>)
    /// </summary>
    public static Task<DbCommand> CreateCommandAsync(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _CreateCommandAsync(conn, sql, (FormattableString[]?)null, null, cancellationToken);
    }
    /// <summary>
    /// 使用 sql语句(<paramref name="sql"/>) 创建数据库执行命令(<see cref="IDbCommand"/>)
    /// </summary>
    public static Task<DbCommand> CreateCommandAsync(this DbConnection conn, params FormattableString[] sqls)
    {
        var cancellationToken = DbInterceptors.GetCancellationToken(CancellationToken.None);
        return _CreateCommandAsync(conn, null, sqls, null, cancellationToken);
    }
    /// <summary>
    /// 使用 sql语句(<paramref name="sql"/>) 创建数据库执行命令(<see cref="IDbCommand"/>)
    /// </summary>
    public static Task<DbCommand> CreateCommandAsync(this DbConnection conn, CancellationToken cancellationToken, params FormattableString[] sqls)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _CreateCommandAsync(conn, null, sqls, null, cancellationToken);
    }


    private static async Task<DbCommand> _CreateCommandAsync(this DbConnection conn, FormattableString? sql, FormattableString[]? sqls, Action<DbCommand>? processCmd, CancellationToken cancellationToken)
    {

        ArgumentNullException.ThrowIfNull(conn);

        if (string.IsNullOrWhiteSpace(sql?.Format) && (sqls is null or { Length: 0 }))
        {
            throw new ArgumentNullException(nameof(sql));
        }
        var timestamp = Stopwatch.GetTimestamp();
        var cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.Connection ??= conn;

        using (ObjectPoolHelper.GetDbParameterFormatProvider(out var provider))
        {
            provider.SetCommand(cmd);
            if (sql is not null)
            {
                var text = string.Format(provider, sql.Format, sql.GetArguments());
                cmd.CommandText = text;
            }
            if (sqls is { Length: > 0 })
            {
                if (cmd.CommandText?.Length > 0 && !cmd.CommandText.EndsWith(";"))
                {
                    cmd.CommandText += ";\n";
                }
                cmd.CommandText += string.Join(";\n", sqls.Select(x => string.Format(provider, x.Format, x.GetArguments()).Trim(';'))) + ";";
            }
        }
        processCmd?.Invoke(cmd);
        _ = await DbInterceptors.OnCommandCreated(() => new(cmd, timestamp, cancellationToken));
        return cmd;
    }

    /// <summary>
    /// 执行事务, 除非 <paramref name="func"/> 抛出异常, 否则直接提交
    /// <para> 如果数据库连接未打开, 则打开, 执行完毕后关闭连接 </para>
    /// <para> 如果数据库连接已打开则直接执行委托, 执行完毕后也不关闭连接 </para>
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="func"></param>
    public static Task<T> TransactionAsync<T>(this DbConnection conn, Func<Task<T>> func, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteAsync(conn, async opening =>
        {
            using var tran = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
            opening.Add(tran);
            var result = await func();
            tran.Complete();
            return result;
        }, cancellationToken);
    }

    /// <summary>
    /// 执行sql, 处理 <see cref="DbDataReader"/>
    /// </summary>
    public static Task ExecuteReaderAsync(this DbConnection conn, FormattableString sql, Func<DbDataReader, Task> action, CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(action);
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteAsync(conn, async opening =>
        {
            var command = await _CreateCommandAsync(conn, sql, null, null, cancellationToken);
            opening.Add(command);
            await _ExecuteReaderAsync(command, action, cancellationToken);
        }, cancellationToken);
    }

    /// <summary>
    /// 执行 <see cref="DbCommand"/>, 处理 <see cref="DbDataReader"/>
    /// </summary>
    public static Task ExecuteReaderAsync(this DbCommand command, Func<DbDataReader, Task> action, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(command, action, cancellationToken);
    }

    private static async Task _ExecuteReaderAsync(this DbCommand command, Func<DbDataReader, Task> action, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(command);
        ArgumentNullException.ThrowIfNull(action);
        var timestamp = Stopwatch.GetTimestamp();
        var reader = await command.ExecuteReaderAsync(cancellationToken);
        await using var reading = await DataReaderScope.Create(reader, command, timestamp, cancellationToken);
        await action(reader);
    }

    /// <summary>
    /// 执行sql, 处理 <see cref="DbDataReader"/>, 并返回数据
    /// </summary>
    public static Task<T> ExecuteReaderAsync<T>(this DbConnection conn, FormattableString sql, Func<DbDataReader, Task<T>> func, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(conn, sql, func, cancellationToken);
    }

    private static Task<T> _ExecuteReaderAsync<T>(this DbConnection conn, FormattableString sql, Func<DbDataReader, Task<T>> func, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(sql);
        ArgumentNullException.ThrowIfNull(func);

        return _ExecuteAsync(conn, async opening =>
        {
            await using var command = await _CreateCommandAsync(conn, sql, null, null, cancellationToken);
            opening.Add(command);
            return await _ExecuteReaderAsync(command, func, cancellationToken);
        }, cancellationToken);
    }

    /// <summary>
    /// 执行<see cref="DbCommand"/>, 处理 <see cref="DbDataReader"/>, 并返回数据
    /// </summary>
    public static Task<T> ExecuteReaderAsync<T>(this DbCommand command, Func<DbDataReader, Task<T>> func, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(command, func, cancellationToken);
    }

    private static async Task<T> _ExecuteReaderAsync<T>(this DbCommand command, Func<DbDataReader, Task<T>> func, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(command);
        ArgumentNullException.ThrowIfNull(func);
        var timestamp = Stopwatch.GetTimestamp();
        var reader = await command.ExecuteReaderAsync(cancellationToken);
        await using var reading = await DataReaderScope.Create(reader, command, timestamp, cancellationToken);
        var result = await func(reader);
        reading.SetResult(result);
        return result;
    }

    /// <summary>
    /// 执行查询命令返回受影响行数
    /// </summary>
    /// <returns></returns>
    public static Task<int> ExecuteNonQueryAsync(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteNonQueryAsync(conn, sql, (FormattableString[]?)null, cancellationToken);
    }

    /// <summary>
    /// 执行查询命令返回受影响行数
    /// </summary>
    /// <returns></returns>
    public static Task<int> ExecuteNonQueryAsync(this DbConnection conn, params FormattableString[] sqls)
    {
        var cancellationToken = DbInterceptors.GetCancellationToken(CancellationToken.None);
        return _ExecuteNonQueryAsync(conn, null, sqls, cancellationToken);
    }

    /// <summary>
    /// 执行查询命令返回受影响行数
    /// </summary>
    /// <returns></returns>
    public static Task<int> ExecuteNonQueryAsync(this DbConnection conn, CancellationToken cancellationToken, params FormattableString[] sqls)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteNonQueryAsync(conn, null, sqls, cancellationToken);
    }

    /// <summary>
    /// 执行查询命令返回受影响行数
    /// </summary>
    /// <returns></returns>
    internal static Task<int> _ExecuteNonQueryAsync(this DbConnection conn, FormattableString? sql, FormattableString[]? sqls, CancellationToken cancellationToken)
    {
        return _ExecuteAsync(conn, async opening =>
        {
            var timestamp = Stopwatch.GetTimestamp();
            if (sqls?.Length > 1)
            {
                var provider = DbProviderFactories.GetFactory(conn);
                if (provider?.CanCreateBatch is true)
                {
                    var batch = provider.CreateBatch();
                    opening.Add(batch);
                    batch.Connection = conn;
                    foreach (var item in sqls)
                    {
                        await using var command = await _CreateCommandAsync(conn, item, null, null, cancellationToken);
                        var cmd = batch.CreateBatchCommand();
                        cmd.CommandText = command.CommandText;
                        cmd.CommandType = command.CommandType;
                        var array = new DbParameter[command.Parameters.Count];
                        command.Parameters.CopyTo(array, 0);
                        cmd.Parameters.AddRange(array);
                        batch.BatchCommands.Add(cmd);
                    }

                    var result = await batch.ExecuteNonQueryAsync(cancellationToken);
                    _ = await DbInterceptors.OnNonQueryExecuted(() => new(batch, timestamp, result, cancellationToken));
                    return result;
                }
            }

            {
                await using var command = await _CreateCommandAsync(conn, sql, sqls, null, cancellationToken);
                opening.Add(command);
                var result = await command.ExecuteNonQueryAsync(cancellationToken);
                _ = await DbInterceptors.OnNonQueryExecuted(() => new(command, timestamp, result, cancellationToken));
                return result;
            }
        }, cancellationToken);
    }



    /// <summary>
    /// 执行sql, 返回实体类集合
    /// </summary>
    public static Task<List<T>> ExecuteListAsync<T>(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken = default, int limit = DEFAULT_LIMIT)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(conn, sql, reader => reader._ReadListAsync<T>(limit, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 执行 <see cref="DbCommand"/>, 返回实体类集合
    /// </summary>
    public static Task<List<T>> ExecuteListAsync<T>(this DbCommand command, CancellationToken cancellationToken = default, int limit = DEFAULT_LIMIT)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(command, reader => reader._ReadListAsync<T>(limit, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 读取 <see cref="DbDataReader"/> 中的数据，并返回动态集合 <see cref="List{dynamic}"/>
    /// </summary>
    public static Task<List<T>> ReadListAsync<T>(this DbDataReader reader, CancellationToken cancellationToken = default, int limit = DEFAULT_LIMIT)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ReadListAsync<T>(reader, limit, cancellationToken);
    }
    private static async Task<List<T>> _ReadListAsync<T>(this DbDataReader reader, int limit, CancellationToken cancellationToken)
    {
        if (typeof(object) == typeof(T))
        {
            return (List<T>)(object)await reader._ReadListAsync(limit, cancellationToken);
        }

        return EntityBuilder.GetBuilder<T>().ToMultiple(reader).Take(limit).ToList();
    }

    /// <summary>
    /// 执行sql, 返回动态对象集合
    /// </summary>
    public static Task<List<dynamic>> ExecuteListAsync(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken = default, int limit = DEFAULT_LIMIT)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(conn, sql, reader => reader._ReadListAsync(limit, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 执行 <see cref="DbCommand"/>, 返回动态对象集合
    /// </summary>
    public static Task<List<dynamic>> ExecuteListAsync(this DbCommand command, CancellationToken cancellationToken = default, int limit = DEFAULT_LIMIT)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(command, reader => reader._ReadListAsync(limit, cancellationToken), cancellationToken);
    }
    /// <summary>
    /// 读取 <see cref="DbDataReader"/> 中的数据，并返回动态集合 <see cref="List{dynamic}"/>
    /// </summary>
    /// <param name="reader"></param>
    /// <param name="limit"></param>
    /// <returns></returns>
    public static Task<List<dynamic>> ReadListAsync(this DbDataReader reader, CancellationToken cancellationToken = default, int limit = DEFAULT_LIMIT)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ReadListAsync(reader, limit, cancellationToken);
    }

    private static async Task<List<dynamic>> _ReadListAsync(this DbDataReader reader, int limit, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(reader);
        var mapping = reader.GetFieldMapping();
        var list = new List<dynamic>();
        for (var i = 0; i < limit && await reader.ReadAsync(cancellationToken); i++)
        {
            var values = new object[reader.FieldCount];
            reader.GetValues(values);
            list.Add(new DynamicRecord(mapping, values));
        }
        return list;
    }

    /// <summary>
    /// 执行sql, 返回第一行数据的实体类型对象
    /// </summary>
    public static Task<T?> ExecuteFirstAsync<T>(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteAsync(conn, async opening =>
        {
            var command = await _PagingCommandAsync(conn, sql, 0, 1, cancellationToken);
            opening.Add(command);
            return await _ExecuteFirstAsync<T>(command, cancellationToken);
        }, cancellationToken);
    }

    /// <summary>
    /// 执行 <see cref="DbCommand"/>, 返回第一行数据的实体类型对象
    /// </summary>
    public static Task<T?> ExecuteFirstAsync<T>(this DbCommand command, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteFirstAsync<T>(command, cancellationToken);
    }

    private static Task<T?> _ExecuteFirstAsync<T>(this DbCommand command, CancellationToken cancellationToken)
    {
        return _ExecuteReaderAsync(command, async reader => await reader.ReadAsync(cancellationToken) ? EntityBuilder.GetBuilder<T>().ToSingle(reader) : default, cancellationToken);
    }

    /// <summary>
    /// 读取 <see cref="DbDataReader"/> 中下一行的数据，并返回实体类型对象
    /// </summary>]
    /// <returns></returns>
    public static async Task<T?> ReadSingleAsync<T>(this DbDataReader reader, CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(reader);
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return await reader.ReadAsync(cancellationToken) ? reader.ToSingle<T>() : default;
    }

    /// <summary>
    /// 执行sql, 返回第一行数据的动态对象
    /// </summary>
    /// <returns></returns>
    public static Task<dynamic> ExecuteFirstAsync(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteAsync(conn, async opening =>
        {
            var command = await _PagingCommandAsync(conn, sql, 0, 1, cancellationToken);
            opening.Add(command);
            return await _ExecuteFirstAsync(command, cancellationToken);
        }, cancellationToken);
    }

    /// <summary>
    /// 执行 <see cref="DbCommand"/>, 返回第一行数据的动态对象
    /// </summary>
    public static Task<dynamic> ExecuteFirstAsync(this DbCommand command, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteFirstAsync(command, cancellationToken);
    }
    private static Task<dynamic> _ExecuteFirstAsync(this DbCommand command, CancellationToken cancellationToken)
    {
        return _ExecuteReaderAsync(command, reader => reader.ReadSingleAsync(cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 读取 <see cref="DbDataReader"/> 中下一行的数据，并返回动态类型对象
    /// </summary>]
    /// <returns></returns>
    public static async Task<dynamic> ReadSingleAsync(this DbDataReader reader, CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(reader);
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return await reader.ReadAsync(cancellationToken) ? reader.ToSingle() : null;
    }

    /// <summary>
    /// 执行sql, 返回第一行第一列的数据
    /// </summary>
    public static Task<T?> ExecuteScalarAsync<T>(this DbConnection conn, FormattableString sql, T? defaultValue = default, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(conn, sql, reader => reader._ReadScalarAsync(defaultValue, cancellationToken), cancellationToken);
    }


    /// <summary>
    /// 执行sql, 返回第一行第一列的数据
    /// </summary>
    public static Task<T?> ExecuteScalarAsync<T>(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken, T? defaultValue = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(conn, sql, reader => reader._ReadScalarAsync(defaultValue, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 执行 <see cref="DbCommand"/>, 返回第一行第一列的数据
    /// </summary>
    public static Task<T?> ExecuteScalarAsync<T>(this DbCommand command, T? defaultValue = default, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(command, reader => reader._ReadScalarAsync(defaultValue, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 执行 <see cref="DbCommand"/>, 返回第一行第一列的数据
    /// </summary>
    public static Task<T?> ExecuteScalarAsync<T>(this DbCommand command, CancellationToken cancellationToken, T? defaultValue = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return ExecuteScalarAsync(command, cancellationToken, defaultValue);
    }


    /// <summary>
    /// 读取 <see cref="DbDataReader"/> 中下一行第一列的数据
    /// </summary>
    public static async Task<T?> ReadScalarAsync<T>(this DbDataReader reader, T? defaultValue = default, CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(reader);
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return await reader.ReadAsync(cancellationToken) ? reader.ToScalar<T>() : defaultValue;
    }

    private static Task<T?> _ExecuteScalarAsync<T>(this DbCommand command, T? defaultValue, CancellationToken cancellationToken)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(command, reader => reader._ReadScalarAsync(defaultValue, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 读取 <see cref="DbDataReader"/> 中下一行第一列的数据
    /// </summary>
    public static Task<T?> ReadScalarAsync<T>(this DbDataReader reader, CancellationToken cancellationToken, T? defaultValue = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return reader._ReadScalarAsync(defaultValue, cancellationToken);
    }
    private static async Task<T?> _ReadScalarAsync<T>(this DbDataReader reader, T? defaultValue, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(reader);
        return await reader.ReadAsync(cancellationToken) ? reader.ToScalar<T>() : defaultValue;
    }

    /// <summary>
    /// 执行sql, 返回第一行第一列的动态对象
    /// </summary>
    public static Task<dynamic> ExecuteScalarAsync(this DbConnection conn, FormattableString sql, object? defaultValue = null, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(conn, sql, reader => reader._ReadScalarAsync(defaultValue, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 执行 <see cref="DbCommand"/>, 返回第一行第一列的动态对象
    /// </summary>
    public static Task<dynamic> ExecuteScalarAsync(this DbCommand command, object? defaultValue = null, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(command, reader => reader._ReadScalarAsync(defaultValue, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 读取 <see cref="DbDataReader"/> 中下一行第一列的动态对象
    /// </summary>
    public static async Task<dynamic> ReadScalarAsync(this DbDataReader reader, object? defaultValue = null, CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(reader);
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return await reader.ReadAsync(cancellationToken) ? reader.ToScalar() : new DynamicAtom(defaultValue);
    }


    /// <summary>
    /// 执行sql, 返回第一行第一列的动态对象
    /// </summary>
    public static Task<dynamic> ExecuteScalarAsync(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken, object? defaultValue = null)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(conn, sql, reader => reader._ReadScalarAsync(defaultValue, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 执行 <see cref="DbCommand"/>, 返回第一行第一列的动态对象
    /// </summary>
    public static Task<dynamic> ExecuteScalarAsync(this DbCommand command, CancellationToken cancellationToken, object? defaultValue = null)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(command, reader => reader._ReadScalarAsync(defaultValue, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 读取 <see cref="DbDataReader"/> 中下一行第一列的动态对象
    /// </summary>
    public static Task<dynamic> ReadScalarAsync(this DbDataReader reader, CancellationToken cancellationToken, object? defaultValue = null)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return reader._ReadScalarAsync(defaultValue, cancellationToken);
    }

    private static async Task<dynamic> _ReadScalarAsync(this DbDataReader reader, object? defaultValue, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(reader);
        return await reader.ReadAsync(cancellationToken) ? reader.ToScalar() : new DynamicAtom(defaultValue);
    }

    /// <summary>
    /// 执行sql, 返回 <see cref="DataTable"/>
    /// </summary>
    public static Task<DataTable> ExecuteDataTableAsync(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken = default, int limit = DEFAULT_LIMIT)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(conn, sql, reader => reader._ReadDataTableAsync(limit, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 执行 <see cref="DbCommand"/>, 返回 <see cref="DataTable"/>
    /// </summary>
    public static Task<DataTable> ExecuteDataTableAsync(this DbCommand command, CancellationToken cancellationToken = default, int limit = DEFAULT_LIMIT)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(command, reader => reader._ReadDataTableAsync(limit, cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 读取 <see cref="DbDataReader"/> 中的数据，并返回 <see cref="DataTable"/>
    /// </summary>
    public static Task<DataTable> ReadDataTableAsync(this DbDataReader reader, CancellationToken cancellationToken = default, int limit = DEFAULT_LIMIT)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ReadDataTableAsync(reader, limit, cancellationToken);
    }

    private static async Task<DataTable> _ReadDataTableAsync(this DbDataReader reader, int limit, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(reader);
        var table = reader.ToDataTable();
        var values = new object[reader.FieldCount];
        for (var i = 0; i < limit && await reader.ReadAsync(cancellationToken); i++)
        {
            reader.GetValues(values);
            table.LoadDataRow(values, LoadOption.OverwriteChanges);
        }
        return table;
    }

    /// <summary>
    /// 执行sql, 返回 <see cref="DataRow"/>
    /// </summary>
    public static Task<DataRow?> ExecuteDataRowAsync(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(conn, sql, reader => reader._ReadDataRowAsync(cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 执行sql, 返回 <see cref="DataRow"/>
    /// </summary>
    public static Task<DataRow?> ExecuteDataRowAsync(this DbCommand command, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteReaderAsync(command, reader => reader._ReadDataRowAsync(cancellationToken), cancellationToken);
    }

    /// <summary>
    /// 读取 <see cref="DbDataReader"/> 中下一行
    /// </summary>
    public static Task<DataRow?> ReadDataRowAsync(this DbDataReader reader, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ReadDataRowAsync(reader, cancellationToken);
    }

    private static async Task<DataRow?> _ReadDataRowAsync(this DbDataReader reader, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(reader);
        return await reader.ReadAsync(cancellationToken) ? reader.ToDataRow() : null;
    }

    /// <summary>
    /// 返回执行表名的表结构
    /// </summary>
    public static Task<DataTable> GetSchemaTableAsync(this DbConnection connection, string tableName, CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(connection);
        if (string.IsNullOrWhiteSpace(tableName))
        {
            throw new ArgumentException($"“{nameof(tableName)}”不能为 null 或空白。", nameof(tableName));
        }
        return _ExecuteAsync(connection, async opening =>
        {
            var timestamp1 = Stopwatch.GetTimestamp();
            var command = connection.CreateCommand();
            opening.Add(command);
            command.CommandText = $"SELECT * FROM {tableName} WHERE 1 = 2";
            command.CommandType = CommandType.Text;
            _ = await DbInterceptors.OnCommandCreated(() => new(command, timestamp1, cancellationToken));
            var timestamp2 = Stopwatch.GetTimestamp();
            var reader = await command.ExecuteReaderAsync(CommandBehavior.SchemaOnly, cancellationToken);
            opening.Add(reader);
            await using var reading = await DataReaderScope.Create(reader, command, timestamp2, cancellationToken);
            var schemaTable = (await reader.GetSchemaTableAsync(cancellationToken)) ?? throw new InvalidOperationException("无法获取表结构");
            schemaTable.TableName = tableName;
            return schemaTable;
        });
    }
}
